﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Meqs.Logic.Core
{
    /// <summary>
    /// sql server 操作类
    /// dzf
    /// </summary>
    internal class MsSQL : DBAccess
    {
        //
        /// <summary>
        /// 构造
        /// </summary>
        /// <param name="type"></param>
        public MsSQL(DBType type)
            : base(type)
        {
        }
        //
        /// <summary>
        /// 初始化连接
        /// </summary>
        /// <param name="strServer"></param>
        /// <param name="port"></param>
        /// <param name="strDataBase"></param>
        /// <param name="strUser"></param>
        /// <param name="strPwd"></param>
        public override void InitConn(string strServer, string port, string strDataBase, string strUser, string strPwd)
        {
            string strConn = CON_strServer + " = " + strServer + "," + port + ";";
            strConn += CON_strDataBase + " = " + strDataBase + ";";
            strConn += CON_strUser + " = " + strUser + ";";
            strConn += CON_strPwd + " = " + strPwd + ";";
            strConn += CON_strConnTimeOut;
            base.m_oConn = new SqlConnection(strConn);
        }
        //
        /// <summary>
        /// 初始化连接
        /// </summary>
        /// <param name="strServer"></param>
        public override void InitConn(string strServer)
        {
            string strConn = strServer;
            base.m_oConn = new SqlConnection(strConn);
        }
        /// <summary>
        /// 获得CMD
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        public override DbCommand GetCmd(string strCmd)
        {
            return new SqlCommand(strCmd, (SqlConnection)base.m_oConn, (SqlTransaction)base._Sign);
        }
        /// <summary>
        /// 获得数据适配器
        /// </summary>
        public override DbDataAdapter DbAdp
        {
            get { return new SqlDataAdapter(); }
        }
        /// <summary>
        /// 获取所有表的信息
        /// </summary>
        public override DataTable Tables
        {
            get
            {
                DataTable dt = null;
                try
                {
                    dt = ((SqlConnection)base.m_oConn).GetSchema("Tables", null);
                }
                catch (Exception ex)
                {
                    ThrowException(ex);
                }
                return dt;
            }
        }
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="strCmd"></param>
        /// <param name="name"></param>
        /// <param name="image"></param>
        /// <param name="len"></param>
        /// <returns></returns>
        public override int RunNoQuery(string strCmd, string name, byte[] image, long len)
        {
            int iRet = 0;
            try
            {
                SqlCommand oCmd = (SqlCommand)GetCmd(strCmd);
                oCmd.Parameters.Add(name, SqlDbType.Image, (int)len);
                oCmd.Parameters[name].Value = image;
                //OleDbParameter pt=new OleDbParameter();
                //pt.OleDbType
                //oCmd.Parameters.Add
                if (oCmd != null)
                {
                    iRet = oCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                ThrowException(ex, strCmd);
                //throw (new Exception(ex.Message));
            }
            return iRet;
        }
        /// <summary>
        /// 获得所有列的信息
        /// </summary>
        /// <returns></returns>
        public override DataTable GetColumns()
        {
            DataTable dt = null;
            try
            {
                dt = ((SqlConnection)base.m_oConn).GetSchema("Columns", null);
            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }
            return dt;

        }
        /// <summary>
        /// 获得某张表的列信息
        /// </summary>
        /// <param name="strTable"></param>
        /// <returns></returns>
        public override DataTable GetColumns(string strTable)
        {
            DataTable dt = null;
            try
            {
                dt = ((SqlConnection)base.m_oConn).GetSchema("Columns", new string[] { null, null, strTable, null });
            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }
            return dt;
            // return ((SqlConnection)base.m_oConn).GetSchema("Columns", new string[] { null, null, strTable, null });
        }
        /// <summary>
        /// 执行非查询存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="ParaValue"></param>
        /// <param name="ParaName"></param>
        /// <param name="ParaType"></param>
        /// <param name="ParaDire"></param>
        public override void ExecProcNonQuery(string procName, object[] ParaValue, string[] ParaName, DBParamerType[] ParaType, DBParameterDirection[] ParaDire)
        {

            try
            {
                DbCommand oCmd = GetCmd(procName);
                oCmd.CommandType = CommandType.StoredProcedure;
                int paraNum = ParaValue.Length;
                for (int i = 0; i < paraNum; i++)
                {
                    SqlParameter pt = new SqlParameter();
                    ParaName[i] = "@" + ParaName[i];
                    //参数名字
                    pt.ParameterName = ParaName[i];
                    pt.SourceColumn = ParaName[i];
                    pt.SqlDbType = GetType(ParaType[i]);
                    pt.Value = ParaValue[i];
                    pt.Direction = (ParameterDirection)ParaDire[i];
                    oCmd.Parameters.Add(pt);
                }
                if (oCmd != null)
                {
                    oCmd.ExecuteNonQuery();
                }
                for (int i = 0; i < paraNum; i++)
                {
                    if (ParaDire[i] != DBParameterDirection.Input)
                    {
                        ParaValue[i] = oCmd.Parameters[ParaName[i]].Value.ToString().Trim();
                    }
                }
            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }


        }
        /// <summary>
        /// 进行更新
        /// </summary>
        /// <param name="strTblName"></param>
        /// <param name="pk"></param>
        /// <param name="row"></param>
        /// <param name="columns"></param>
        /// <param name="names"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        private string WithUpdate(string strTblName, string pk, DataRow row, DataColumnCollection columns, ref string[] names, ref object[] obj)
        {
            //string exsql = "update " + strTblName + " set ";
            //string sed = "where ";
            //foreach (DataColumn column in columns)
            //{
            //    if (column.Caption.ToLower() != pk.ToLower())
            //    {
            //        exsql += " " + column.Caption + "='" + @row[column] + "' ,";
            //    }
            //    else
            //    {
            //        sed += column.Caption + "='" + @row[column] + "'";
            //    }
            //}
            //exsql = exsql.Substring(0, exsql.Length - 1);
            //exsql += sed;


            string exsql = "update " + strTblName + " set ";
            string sed = "where ";
            int index = 0;
            foreach (DataColumn column in columns)
            {
                if (column.Caption.ToLower() != pk.ToLower())
                {
                    exsql += " " + column.Caption + "=@" + index + " ,";
                }
                else
                {
                    sed += column.Caption + "=@" + index + "";
                }
                index++;
            }
            names = new string[index];
            obj = new object[index];
            index = 0;
            foreach (DataColumn column in columns)
            {

                names[index] = "@" + index;
                obj[index] = row[column];
                index++;

            }
            exsql = exsql.Substring(0, exsql.Length - 1);
            exsql += sed;
            return exsql;
        }
        /// <summary>
        /// 进行插入
        /// </summary>
        /// <param name="strTblName"></param>
        /// <param name="pk"></param>
        /// <param name="row"></param>
        /// <param name="columns"></param>
        /// <param name="names"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        private string WithInsert(string strTblName, string pk, DataRow row, DataColumnCollection columns, ref string[] names, ref object[] obj)
        {
            //string exsql = "insert into " + strTblName + " (";
            //string values = " values(";
            //foreach (DataColumn column in columns)
            //{
            //    if (column.Caption.ToLower() != pk.ToLower())
            //    {
            //        exsql += " " + column.Caption + ",";// +"='" + @row[column] + "' ,";
            //        values += "'" + @row[column] + "' ,";
            //    }
            //}
            //exsql = exsql.Substring(0, exsql.Length - 1);
            //values = values.Substring(0, values.Length - 1);
            //exsql += ") ";
            //values += ")";
            //exsql += values;
            //Console.WriteLine(exsql);

            string exsql = "insert into " + strTblName + " (";
            string values = " values(";
            int index = 0;
            foreach (DataColumn column in columns)
            {
                if (column.Caption.ToLower() != pk.ToLower())
                {
                    exsql += " " + column.Caption + ",";// +"='" + @row[column] + "' ,";
                    values += "@" + index + " ,";
                    index++;
                }

            }
            names = new string[index];
            obj = new object[index];
            index = 0;
            foreach (DataColumn column in columns)
            {
                if (column.Caption.ToLower() != pk.ToLower())
                {
                    names[index] = "@" + index;
                    obj[index] = row[column];
                    index++;
                }
            }
            exsql = exsql.Substring(0, exsql.Length - 1);
            values = values.Substring(0, values.Length - 1);
            exsql += ") ";
            values += ")";
            exsql += values;
            //Console.WriteLine(exsql);


            return exsql;
        }
        /// <summary>
        /// 进行更新表
        /// </summary>
        /// <param name="strTblName"></param>
        /// <param name="dt"></param>
        /// <param name="pk"></param>
        /// <returns></returns>
        public override int UpdateDataTable(string strTblName, DataTable dt, string pk)
        {
            int re = 0;
            string[] names = null;
            object[] obj = null;
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow row in dt.Rows)
                    {
                        string exsql = null;
                        switch (row.RowState)
                        {
                            case DataRowState.Added:
                                exsql = WithInsert(strTblName, pk, row, dt.Columns, ref names, ref obj);
                                break;
                            case DataRowState.Modified:
                                exsql = WithUpdate(strTblName, pk, row, dt.Columns, ref names, ref obj);
                                break;
                        }

                        //string exsql = "update " + strTblName + " set ";
                        //string sed = "where ";
                        //foreach (DataColumn column in dt.Columns)
                        //{
                        //    if (column.Caption.ToLower() != pk.ToLower())
                        //    {
                        //        exsql += " " + column.Caption + "='" + @row[column] + "' ,";
                        //    }
                        //    else
                        //    {
                        //        sed += column.Caption + "='" + @row[column] + "'";
                        //    }
                        //}
                        //exsql = exsql.Substring(0, exsql.Length - 1);
                        //exsql += sed;

                        if (exsql != null)
                        {
                            try
                            {
                                re += RunNoQuery(exsql, names, obj);
                                //continue;
                            }
                            catch
                            {
                                continue;
                            }
                        }

                    }
                }
            }

            return re;
        }
        /// <summary>
        /// 执行查询存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="ParaValue"></param>
        /// <param name="ParaName"></param>
        /// <param name="ParaType"></param>
        /// <param name="ParaDire"></param>
        /// <returns></returns>
        public override DataTable ExecProcQuery(string procName, object[] ParaValue, string[] ParaName, DBParamerType[] ParaType, DBParameterDirection[] ParaDire)
        {
            DataTable dt = new DataTable();
            try
            {

                DbCommand oCmd = GetCmd(procName);
                oCmd.CommandType = CommandType.StoredProcedure;
                int paraNum = ParaValue.Length;
                for (int i = 0; i < paraNum; i++)
                {
                    SqlParameter pt = new SqlParameter();
                    ParaName[i] = "@" + ParaName[i];
                    //参数名字
                    pt.ParameterName = ParaName[i];
                    pt.SourceColumn = ParaName[i];
                    pt.SqlDbType = GetType(ParaType[i]);
                    pt.Value = ParaValue[i];
                    pt.Direction = (ParameterDirection)ParaDire[i];
                    oCmd.Parameters.Add(pt);
                }
                if (oCmd != null)
                {
                    DbDataAdapter adp = this.DbAdp;
                    adp.SelectCommand = oCmd;
                    adp.Fill(dt);
                }
                for (int i = 0; i < paraNum; i++)
                {
                    if (ParaDire[i] != DBParameterDirection.Input)
                    {
                        ParaValue[i] = oCmd.Parameters[ParaName[i]].Value.ToString().Trim();
                    }
                }
            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }


            return dt;
        }
        /// <summary>
        /// 获得类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private SqlDbType GetType(DBParamerType type)
        {

            switch (type)
            {
                case DBParamerType.BigInt:
                    return SqlDbType.BigInt;
                case DBParamerType.Binary:
                    return SqlDbType.Binary;
                case DBParamerType.Char:
                    return SqlDbType.Char;
                case DBParamerType.DBDateTime:
                    return SqlDbType.DateTime;
                case DBParamerType.Decimal:
                    return SqlDbType.Decimal;
                case DBParamerType.Float:
                    return SqlDbType.Float;
                case DBParamerType.SmallInt:
                    return SqlDbType.SmallInt;
                case DBParamerType.TinyInt:
                    return SqlDbType.TinyInt;
                case DBParamerType.VarBinary:
                    return SqlDbType.VarBinary;
                case DBParamerType.VarChar:
                    return SqlDbType.VarChar;
                case DBParamerType.Variant:
                    return SqlDbType.Variant;
                case DBParamerType.Integer:
                    return SqlDbType.Int;
                default:
                    return SqlDbType.VarChar;
            }
        }
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="insertStrCmd"></param>
        /// <param name="name"></param>
        /// <param name="paramer"></param>
        /// <param name="newNum"></param>
        /// <returns></returns>
        public override int RunNoQuery(string insertStrCmd, string[] name, object[] paramer, ref int newNum)
        {
            int num = 0;
            if (name != null)
            {
                num = name.Length;
            }


            try
            {
                SqlCommand oCmd = (SqlCommand)GetCmd(insertStrCmd + ";SELECT scope_identity() AS ID");
                for (int i = 0; i < num; i++)
                {
                    oCmd.Parameters.Add(name[i], paramer[i]);
                }
                DataTable dt = new DataTable();
                DbDataAdapter adp = this.DbAdp;
                adp.SelectCommand = oCmd;
                adp.Fill(dt);
                if (dt.Rows[0] != null)
                    newNum = (int)(decimal)dt.Rows[0][0];
                else
                    newNum = -1;
            }
            catch (Exception ex)
            {
                ThrowException(ex, insertStrCmd);
                //throw (new Exception(ex.Message));
            }
            return 1;
        }
        /// <summary>
        /// 执行非查询
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        public override int RunNoQuery(string strCmd)
        {
            int iRet = 0;
            try
            {
                DbCommand oCmd = this.GetCmd(strCmd);
                //OleDbParameter pt=new OleDbParameter();
                //pt.OleDbType
                //oCmd.Parameters.Add
                if (oCmd != null)
                {
                    iRet = oCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                ThrowException(ex, strCmd);
                //throw (new Exception(ex.Message));
            }
            return iRet;
        }
        /// <summary>
        /// 获取字段最大值
        /// </summary>
        /// <param name="strTable"></param>
        /// <param name="strField"></param>
        /// <returns></returns>
        public override int GetFiledMax(string strTable, string strField)
        {
            int iRet = -1;
            DataTable dt;
            try
            {
                dt = this.RunQuery("Select Max(" + strField + ") From " + strTable);
                if (dt != null && dt.Rows.Count == 1)
                {
                    iRet = dt.Rows[0][0] is DBNull ? 0 : Convert.ToInt32(dt.Rows[0][0]);
                }
            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }

            return iRet;
        }
        /// <summary>
        /// 执行非查询
        /// </summary>
        /// <param name="strCmd"></param>
        /// <param name="name"></param>
        /// <param name="paramer"></param>
        /// <returns></returns>
        public override int RunNoQuery(string strCmd, string[] name, object[] paramer)
        {
            int num = 0;
            if (name != null)
            {
                num = name.Length;
            }
            int iRet = 0;

            try
            {
                SqlCommand oCmd = (SqlCommand)GetCmd(strCmd);
                for (int i = 0; i < num; i++)
                {
                    oCmd.Parameters.Add(name[i], paramer[i]);

                }

                if (oCmd != null)
                {
                    iRet = oCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                ThrowException(ex, strCmd);
                //throw (new Exception(ex.Message));
            }
            return iRet;
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="strCmd"></param>
        /// <returns></returns>
        public override DataTable RunQuery(string strCmd)
        {
            DataTable dt = new DataTable();
            DbDataAdapter adp = this.DbAdp;
            try
            {
                adp.SelectCommand = this.GetCmd(strCmd);
                adp.Fill(dt);
            }
            catch (Exception ex)
            {
                ThrowException(ex, strCmd);
            }

            return dt;
        }
        /// <summary>
        /// 执行查询
        /// </summary>
        /// <param name="strCmd"></param>
        /// <param name="name"></param>
        /// <param name="paramer"></param>
        /// <returns></returns>
        public override DataTable RunQuery(string strCmd, string[] name, object[] paramer)
        {
            DataTable dt = new DataTable();
            DbDataAdapter adp = this.DbAdp;
            int num = 0;
            if (name != null)
            {
                num = name.Length;
            }
            try
            {
                SqlCommand oCmd = (SqlCommand)GetCmd(strCmd);
                for (int i = 0; i < num; i++)
                {
                    oCmd.Parameters.Add(name[i], paramer[i]);

                }
                adp.SelectCommand = oCmd;
                adp.Fill(dt);
            }
            catch (Exception ex)
            {
                ThrowException(ex, strCmd);
            }

            return dt;
        }
        /// <summary>
        /// 开启事务
        /// </summary>
        /// <returns></returns>
        public override object StartTransaction()
        {
            try
            {
                base._Sign = base.m_oConn.BeginTransaction();
            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }

            return base._Sign;
        }
        /// <summary>
        /// 完成事务
        /// </summary>
        /// <param name="sign"></param>
        public override void CommitTransaction(object sign)
        {
            if (sign == null)
                return;
            DbTransaction dbtrans = (DbTransaction)sign;
            try
            {
                dbtrans.Commit();

            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }
            finally
            {
                _Sign = null;
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="sign"></param>
        public override void RollBackTransaction(object sign)
        {
            if (sign == null)
                return;
            DbTransaction dbtrans = (DbTransaction)sign;
            try
            {
                dbtrans.Rollback();

            }
            catch (Exception ex)
            {
                ThrowException(ex);
            }
            finally
            {
                _Sign = null;
            }

        }
        /// <summary>
        /// 关闭链接
        /// </summary>
        public override void Close()
        {
            if (base.m_oConn != null && base.m_oConn.State == System.Data.ConnectionState.Open)
            {
                base.m_oConn.Close();
                //SqlConnection.ClearAllPools();

            }
        }
    }
}